package My

import (
	"context"
	"errors"
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"sort"
	"strconv"
	"strings"
)

var intType = map[string]string{"FLOAT": "int", "DOUBLE": "int", "DECIMAL": "int", "NUMERIC": "int", "TINYINT": "int", "SMALLINT": "int", "MEDIUMINT": "int", "INT": "int", "BIGINT": "int"}
var timeType = map[string]string{"YEAR": "time", "DATE": "time", "TIME": "time", "DATETIME": "time", "TIMESTAMP": "time"}
var stringType = map[string]string{"CHAR": "string", "VARCHAR": "string"}
var typeCost = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}

type statisticsS struct {
	Schema           string `json:"databaseName"`
	Table            string `json:"tableName"`
	IndexName        string `json:"indexName"`
	UniqueType       string `json:"uniqueType"`
	IndexSeq         string `json:"columnSeq"`
	IndexColumn      string `json:"columnName"`
	PreFix           string `json:"prefix"`
	IndexCardinality string `json:"cardinality"`
}
type subInnodbIndexStats struct {
	Rows            string   `json:"tableRows"`
	ColumnName      []string `json:"columnName"`
	StatName        string   `json:"statName"`
	StatValue       string   `json:"statValue"`
	SampleSize      string   `json:"sampleSize"`
	StatDescription string   `json:"statDescription"`
}
type InnodbIndexStats struct {
	IndexName string                `json:"indexName"`
	SubStats  []subInnodbIndexStats `json:"subStats"`
}
type IndexColumnMe struct {
	ColumnType string `json:"columnType"` //--
	ColumnName string `json:"columnName"` //--
	ColumnSeq  string `json:"columnSeq"`
	IndexSeq   string `json:"indexSeq"`
	SubPart    int64  `json:"subPart"` //前缀索引长度
}
type NameColumnMe struct {
	Schema             string          `json:"databaseName"`
	Table              string          `json:"tableName"` //--
	IndexName          string          `json:"indexName"` //--
	Column             []IndexColumnMe `json:"Column"`
	Null               string          `json:"null"`
	AutoIncrement      string          `json:"autoIncrement"`
	NonUnique          string          `json:"nonUnique"`
	Cardinality        int64           `json:"cardinality"`   //--
	ColumnKey          string          `json:"columnKey"`     //--判断是普通索引，还是唯一索引，还是普通索引
	IndexCategory      string          `json:"indexCategory"` //--
	indexMaxLength     int
	indexLengthDetails []int
}

func (im IndexMe) nameColumnBase(ctx context.Context) (colMeta global.Return, err error) {
	cm := ColumnMe{}
	if colMeta, err = cm.Meta(ctx); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	if colMeta, err = cm.MetaMap(context.WithValue(context.Background(), "key", parDef.Parameter{Meta: colMeta.Result})); err != nil {
		err = ref.ErrAddPrintf("nameColumnBase", err)
		return
	}
	return
}

func (im IndexMe) statistics(ctx context.Context) (result global.Return, err error) {
	var event = "[statistics]"
	var res []statisticsS
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, indexStatisticsSysObjectView, getIndexStatisticsMetaSql, QueryColValManage))
		if result.Result == nil {
			return
		}
		var p []map[string]any
		for _, v := range result.Result.([]map[string]any) {
			vv := mapNullActive(v)
			var dd = statisticsS{}
			if err = ref.MapToStruct(vv, &dd); err != nil {
				err = ref.ErrAddPrintf("statistics", err)
				return
			}
			res = append(res, dd)
			p = append(p, vv)
		}
		result.Result, result.InsideResult = p, res
		return
	}
}

func indexNameGroupByColumnName(ss []statisticsS) (result map[string][]string) {
	result = make(map[string][]string)
	for _, v := range ss {
		if _, ok := result[v.IndexName]; !ok {
			result[v.IndexName] = []string{v.IndexColumn}
		} else {
			result[v.IndexName] = append(result[v.IndexName], v.IndexColumn)
		}
	}
	return
}
func indexCardinality(nameResult parDef.IndexColumnMe, indexMeta []statisticsS) (res parDef.IndexColumnMe) {
	var car []int64
	res = nameResult
	for _, vv := range indexMeta {
		if strings.EqualFold(vv.IndexName, nameResult.IndexName) {
			if seq, _ := stringAnyToInt64(vv.IndexSeq); seq > 2 {
				continue
			}
			var r int64
			r, _ = stringAnyToInt64(vv.IndexCardinality)
			car = append(car, r)
		}
	}
	sort.Slice(car, func(i, j int) bool {
		return car[i] > car[j]
	})
	res.Cardinality = car[0]
	return
}
func columnKey(nameResult parDef.IndexColumnMe, indexMeta []statisticsS) (res parDef.IndexColumnMe) {
	var UniqueMark int64
	res = nameResult
	for _, vv := range indexMeta {
		if strings.EqualFold(vv.IndexName, nameResult.IndexName) {
			UniqueMark, _ = stringAnyToInt64(vv.UniqueType)
			break
		}
	}
	switch UniqueMark {
	case 0:
		res.ColumnKey = "U"
		if strings.EqualFold(nameResult.IndexName, "PRIMARY") {
			res.ColumnKey = "P"
		}
		res.NonUnique = "1"
	case 1:
		res.ColumnKey = "I"
		res.NonUnique = "0"
	}
	return
}

// http://mysql.taobao.org/monthly/2022/10/05/
// http://mysql.taobao.org/monthly/2024/01/03/
// http://mysql.taobao.org/monthly/2021/05/03/
// https://www.cnblogs.com/wanbin/p/9554091.html
func indexNameMerge(res []map[string]any) (result map[string][]any) {
	result = make(map[string][]any)
	for _, v := range res {
		var iName = fmt.Sprintf("%v", v["indexName"])
		if _, ok := result[iName]; !ok {
			result[iName] = []any{v}
		} else {
			result[iName] = append(result[iName], v)
		}
	}
	return
}
func indexNameSubOption(ss []any) (result []any) {
	for _, v := range ss {
		vv := mapNullActive(v.(map[string]any))
		statName, ok1 := vv["statName"]
		statDescription, ok2 := vv["statDescription"]
		if ok1 && ok2 && strings.HasPrefix(strings.ToLower(strings.TrimSpace(fmt.Sprintf("%v", statName))), "n_diff_pf") {
			vv["columnName"] = func() (res []any) {
				for _, p := range strings.Split(strings.TrimSpace(fmt.Sprintf("%v", statDescription)), ",") {
					res = append(res, p)
				}
				return
			}()
		}
		result = append(result, vv)
	}
	return
}
func (im IndexMe) ColumnDistinct(ctx context.Context) (result global.Return, err error) {
	var event = "[columnDistinct]"
	defer func() {
		if r := ref.RecoverPanic(event, recover()); err == nil && r != nil {
			err = r
		}
	}()
	select {
	case <-ctx.Done():
		err = ctx.Err()
		return
	default:
		result, err = parDef.SqlExec(parDef.GetSubSqlExecFuncParameterValue(ctx, event, ETL.SMap, seqSysObjectView, getIndexColumnDistinctMetaSql, QueryColValManage))
		var res []InnodbIndexStats
		if result.Result == nil {
			return
		}
		var pp []map[string]any
		for kk, vv := range indexNameMerge(result.Result.([]map[string]any)) {
			var p = make(map[string]any)
			p["indexName"] = kk
			p["subStats"] = indexNameSubOption(vv)
			pp = append(pp, p)
		}
		for _, v := range pp {
			var sub InnodbIndexStats
			if err = ref.MapToStruct(v, &sub); err != nil {
				err = ref.ErrAddPrintf("columnDistinct", err)
				return
			}
			res = append(res, sub)
		}
		result.Result, result.InsideResult = pp, res
		return
	}
}

func IndexColumnInit(nameResult parDef.IndexColumnMe, ss []statisticsS, cc []parDef.ColMetaMapS) (res parDef.IndexColumnMe) {
	var p []parDef.IdxSubColumnMe
	res = nameResult
	for kk, columnGarth := range indexNameGroupByColumnName(ss) {
		if !strings.EqualFold(nameResult.IndexName, kk) {
			continue
		}
		for k, column := range columnGarth {
			for _, columnInfo := range cc {
				if strings.EqualFold(column, columnInfo.ColumnName) {
					p = append(p, parDef.IdxSubColumnMe{
						ColumnName: columnInfo.ColumnName,
						ColumnType: columnInfo.Type.ColTypeMap,
						ColumnSeq:  columnInfo.ColumnSeq,
						ColumnNull: columnInfo.ColNullMap,
						Collation:  columnInfo.CollationName,
						IndexSeq:   strconv.Itoa(k),
					})
				}
			}
		}
	}
	res.ColumnMate = p
	return
}
func NameColumnBaseInit(indexMeta []statisticsS) (result []parDef.IndexColumnMe) {
	var p = make(map[string]int)
	for _, v := range indexMeta {
		if _, ok := p[v.IndexName]; !ok {
			p[v.IndexName]++
			result = append(result, parDef.IndexColumnMe{
				Schema:        v.Schema,
				Table:         v.Table,
				IndexName:     v.IndexName,
				IndexCategory: "local",
			})
		}
	}
	return
}
func (im IndexMe) NameColumn(ctx context.Context) (result global.Return, err error) {
	if result, err = im.nameColumnBase(ctx); err != nil {
		err = ref.ErrAddPrintf("NameColumn", err)
		return
	}
	if result.Result == nil {
		return
	}
	colMeta := result.Result.([]parDef.ColMetaMapS)
	if result, err = im.statistics(ctx); err != nil || result.Result == nil {
		return
	}
	indexMeta := result.InsideResult.([]statisticsS)
	var resStr []parDef.IndexColumnMe
	for _, vv := range NameColumnBaseInit(indexMeta) {
		var p parDef.IndexColumnMe
		p = indexCardinality(vv, indexMeta)
		p = columnKey(p, indexMeta)
		p = IndexColumnInit(p, indexMeta, colMeta)
		resStr = append(resStr, p)
	}
	result.Result = resStr
	return
}

// 按照索引离散度倒序排序（离散度最多两个列）
func (im IndexMe) cardinalityReverseSort(IIData []parDef.IndexColumnMe, Sum int64) (result []string) {
	var cardinality = make(map[string]int64)
	for _, v := range IIData {
		if Sum > 0 && v.Cardinality > 0 && v.Cardinality*100/Sum > 30 {
			cardinality[v.IndexName] = v.Cardinality
		}
	}
	result = ref.MapValueReverseSort(cardinality)
	return
}
func specifyIndexNameToNameColumnMe(specifyIndex string, IIData []parDef.IndexColumnMe) (p parDef.IndexColumnMe) {
	for _, vv := range IIData {
		if strings.EqualFold(specifyIndex, vv.IndexName) {
			p = vv
			return
		}
	}
	return
}
func compoundIndexColumnHistogram(IndexName string, cDistinctMeta any) (hist []int64, sum int64) {
	var err error
	for _, v := range cDistinctMeta.([]InnodbIndexStats) {
		if strings.EqualFold(v.IndexName, IndexName) {
			for k, vv := range v.SubStats {
				sum, _ = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", vv.Rows)), 10, 64)
				if k > 1 || strings.Contains(vv.StatDescription, "DB_ROW_ID") {
					continue
				}
				var hv int64
				if hv, err = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", vv.StatValue)), 10, 64); err != nil {
					hist = append(hist, 0)
				} else {
					hist = append(hist, hv)
				}
			}
		}
	}
	return
}
func (im IndexMe) columnCardinalityStandard(hist []int64, Sum int64) bool {
	for _, vv := range hist {
		if vv*100/Sum > 30 {
			return true
		}
	}
	return false
}

// 比较单列和两个列的直方图，选择合适的
func (im IndexMe) columnHistogram(ctx context.Context, IIData []parDef.IndexColumnMe) (result []string, err error) {
	var columnDistinctMeta global.Return
	if columnDistinctMeta, err = im.ColumnDistinct(ctx); err != nil {
		err = ref.ErrAddPrintf("columnHistogram", err)
		return
	}
	if columnDistinctMeta.Result == nil {
		return
	}
	var cost = make(map[string]any)
	for _, v := range IIData {
		hist, Sum := compoundIndexColumnHistogram(v.IndexName, columnDistinctMeta.InsideResult)
		switch {
		case len(hist) == 1 && hist[0] > 0 && im.columnCardinalityStandard(hist, Sum):
			cost[v.IndexName] = math.Round((float64(Sum)/float64(hist[0]))*100) / 100
		case len(hist) > 1 && hist[0] > 0 && hist[1] > 0 && im.columnCardinalityStandard(hist, Sum):
			var re, factor float64 = 0, 1
			for _, h := range hist {
				re = factor * math.Round((float64(Sum)/float64(h))*100) / 100
				factor = re
			}
			cost[v.IndexName] = re
		}
	}
	result = ref.MapValueSort(cost)
	return
}
func typeSelection(c parDef.IdxSubColumnMe) (cost int) {
	for _, v := range []map[string]string{intType, timeType, stringType} {
		if t, ok := v[strings.ToUpper(strings.Split(c.ColumnType, "(")[0])]; ok {
			return typeCost[t]
		}
	}
	return 100
}
func specifyIndexColumnTypeCost(IIData []parDef.IdxSubColumnMe) (cost int) {
	for _, v := range IIData {
		cost += typeSelection(v)
	}
	return
}
func (im IndexMe) typeScreening(screeningIndex []string, IIData []parDef.IndexColumnMe) (result string) {
	var indexTypeCost = make(map[string]any)
	for _, v := range screeningIndex {
		p := specifyIndexNameToNameColumnMe(v, IIData)
		indexTypeCost[v] = specifyIndexColumnTypeCost(p.ColumnMate)
	}
	res := ref.MapValueSort(indexTypeCost)
	if len(res) >= 1 {
		result = res[0]
	}
	return
}
func (im IndexMe) KeyChoose(ctx context.Context) (result global.Return, err error) {
	var res, indexColumnQueryData global.Return
	var event = "[KeyChoose]"
	var c = SumF{}
	defer func() {
		if r := recover(); r != nil {
			err = ref.ErrAddPrintf(event, errors.New(fmt.Sprintf("defer recover An exception was captured. abnormal is %v ", r)))
		}
	}()
	if res, err = c.Rows(ctx); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if res.Result == nil {
		return
	}
	Sum := res.Result.(int64)
	if indexColumnQueryData, err = im.NameColumn(ctx); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if indexColumnQueryData.Result == nil {
		return
	}
	var IIData = indexColumnQueryData.Result.([]parDef.IndexColumnMe)
	var screeningIndex []string
	if screeningIndex, err = im.columnHistogram(ctx, IIData); err != nil {
		err = ref.ErrAddPrintf("KeyChoose", err)
		return
	}
	if len(screeningIndex) == 0 {
		screeningIndex = im.cardinalityReverseSort(IIData, Sum)
	}
	chooseIndex := im.typeScreening(screeningIndex, IIData)
	for _, v := range IIData {
		if strings.EqualFold(v.IndexName, chooseIndex) {
			result.Result = v
			return
		}
	}
	return
}
func getIndexColumnRemoveSelfAttributes(ctx context.Context, metaBase MetaBaseInitResult) (delG []string, err error) {
	var gipSwitch bool
	var tm = TableMe{}
	var event = "getIndexColumnRemoveSelfAttributes"
	if gipSwitch, err = tm.GetGIPKSwitch(event, ctx); err != nil {
		err = ref.ErrAddPrintf("getIndexColumnRemoveSelfAttributes", err)
		return
	}
	if gipSwitch {
		delG = append(delG, "MY_ROW_ID")
	}
	if metaBase.partMe.PartCol != nil {
		delG = append(delG, metaBase.partMe.PartCol...)
	}
	if metaBase.partMe.SubPartCol != nil {
		delG = append(delG, metaBase.partMe.SubPartCol...)
	}
	return
}
func getIndexColumnRemoveColumnStatus(Col, key string, delG []string) bool {
	switch key {
	case "U", "P":
		for _, dg := range delG {
			if strings.EqualFold(Col, dg) {
				return true
			}
		}
	}
	return false
}
func (im IndexMe) NameToColumnClip(ctx context.Context) (result global.Return, err error) {
	var metaBase MetaBaseInitResult
	var delG []string
	var res = make(map[string][]string)
	if metaBase, err = metaBaseInit(ctx); err != nil {
		err = ref.ErrAddPrintf("NameToColumnClip", err)
		return
	}
	if delG, err = getIndexColumnRemoveSelfAttributes(ctx, metaBase); err != nil {
		err = ref.ErrAddPrintf("NameToColumnClip", err)
		return
	}
	for _, indexCol := range metaBase.indexMeta {
		var colG []string
		for _, p := range indexCol.ColumnMate {
			if getIndexColumnRemoveColumnStatus(p.ColumnName, indexCol.ColumnKey, delG) {
				continue
			}
			var col = strings.ToUpper(p.ColumnName)
			if p.SubPart > 0 {
				col = fmt.Sprintf("%v(%v)", strings.ToUpper(p.ColumnName), p.SubPart)
			}
			colG = append(colG, col)
		}
		if len(colG) == 0 {
			continue
		}
		res[strings.ToUpper(fmt.Sprintf("%v@%v", indexCol.IndexName, indexCol.ColumnKey))] = colG
	}
	result.Result = res
	return
}
